package com.apestech.framework.db;

import java.sql.Connection;
import java.util.ArrayList;

import com.apestech.api.exp.XLSTable;
import com.apestech.api.query.XLSRead;
import com.apestech.api.query.XLSReadCallback;
import com.apestech.formwork.condition.ConditionUtil;
import com.apestech.formwork.xml.XmlQuery;
import com.apestech.framework.db.callback.ExecSqlCallBackHandler;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.db.session.*;
import com.apestech.framework.dictionary.DataDictionary;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.metadata.MetaData;
import com.apestech.framework.metadata.TableInfoCallback;
import com.apestech.framework.sql.condition.ConditionMap;
import com.apestech.framework.util.DBUtil;
import com.apestech.framework.util.FileUtil;
import com.apestech.framework.util.LocalVariable;
import com.apestech.framework.util.modul.DBInfo;
import com.apestech.framework.xml.plugin.XmlPaste;
import com.apestech.framework.xml.plugin.modul.QryConfig;
import com.apestech.framework.xml.plugin.modul.SpecialField;
import com.apestech.framework.xml.sql.SqlNodeVar;
import com.apestech.framework.xml.table.modul.Tables;

public class SqlSession extends SessionUtil {

	public static class Var{
		private String col;
		private String var;
		private String ins;
		private String upt;
		private String sel;
		private String out;

		public String getCol() {
			return col;
		}

		public void setCol(String col) {
			this.col = col;
		}

		public String getVar() {
			return var;
		}

		public void setVar(String var) {
			this.var = var;
		}

		public String getIns() {
			return ins;
		}

		public void setIns(String ins) {
			this.ins = ins;
		}

		public String getUpt() {
			return upt;
		}

		public void setUpt(String upt) {
			this.upt = upt;
		}

		public String getSel() {
			return sel;
		}

		public void setSel(String sel) {
			this.sel = sel;
		}

		public String getOut() {
			return out;
		}

		public void setOut(String out) {
			this.out = out;
		}
	}
	
	private SqlTemplate cn;
	private DBInfo dbInfo;
	private QryConfig qryConfig;
	private ForResultSet forResultSet;
	private ForUpdate forUpdate;
	private ForDDL forDDL;
	private ForJSON forJSON;
	private ForProcedure forProcedure;
	private String theme="esb";
	private SpecialField.Field sysField;
	private XmlQuery xmlQuery;

	public SqlSession(DBInfo dbInfo) throws Exception {
		if(dbInfo==null){
			throw new Exception("数据库相关信息不允许为空！");
		}
		if(DBUtil.getSysSingleSysId()!=null) theme=DBUtil.getSysSingleSysId();
		this.cn=new SqlTemplate(dbInfo);
		this.dbInfo=dbInfo;
		this.forResultSet=new ForResultSet(this.cn, this);
		this.forUpdate=new ForUpdate(this.cn, this);
		this.forDDL=new ForDDL(this);
		this.forJSON=new ForJSON(this);
		this.forProcedure=new ForProcedure(this);
	}

	public void setConnection(Connection conn) throws Exception {
		if(conn==null) this.cn.setConnection(dbInfo.getDataSource().getConnection());
		else {
			this.cn.setConnection(conn) ;
		}
	}

	public Connection getConnection() {
		return cn.getConnection();
	}

	public void closeConnection() throws Exception {
		if(cn.getConnection()!=null){
			cn.getConnection().close();
		}
	}

	public String getDbName(){
		return dbInfo.getDbName();
	}

	public String getDbType(){
		return dbInfo.getDbType();
	}

	public String getSchema(){
		return dbInfo.getSchema();
	}

	public SqlDataSource getDataSource(){
		return dbInfo.getDs();
	}

	public XmlQuery getXmlQuery() {
		return xmlQuery;
	}

	public void setXmlQuery(XmlQuery xmlQuery) {
		this.xmlQuery = xmlQuery;
	}

	//	public SqlParam getSqlparam() {
//		return forResultSet.getSqlparam();
//	}

	public SqlTemplate getCn() {
		return cn;
	}

	public QryConfig getQryConfig() {
		return qryConfig;
	}

	public void setQryConfig(QryConfig qryConfig) {
		this.qryConfig = qryConfig;
		forResultSet.setQryConfig(this.qryConfig);
		forUpdate.setQryConfig(this.qryConfig);
	}

	public Table getDictTable(String tabName) throws Exception {
		if(tabName==null) return null;
		try {
			tabName=tabName.toUpperCase();
			if(!DBUtil.isDbreadonly()){
				Table tab=DataDictionary.getTableFromDB(this, tabName);
				return tab;
			} else {
				Table tab=DataDictionary.getTable(this, tabName);
				return tab;
			}
		} catch (Exception e) {
			try{
				if(e.getMessage().indexOf("ORA-16000")>=0){
					Table tab=DataDictionary.getTable(this, tabName);
					DBUtil.setDbreadonly(true);
					return tab;
				} else {
					throw e;
				}
			} catch (Exception e1){
				throw new Exception(e1);
			}
		}
	}

	public  Table refreshTable(String tabName) throws Exception{
		if(tabName==null) return null;
		try {
			return DataDictionary.refreshTable(this, tabName.toUpperCase());
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	public  JSONArray getProcedure(String procName) throws Exception{
		if(procName==null) return null;
		try {
			return DataDictionary.getProcedure(this, procName.toUpperCase());
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	public  JSONArray refreshProcedure(String procName) throws Exception {
		if(procName==null) return null;
		try {
			return DataDictionary.refreshProcedure(procName.toUpperCase(), this);
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	public  void iniDictionary() throws Exception{
		try {
			DataDictionary.iniDictionary(this);
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	public ForResultSet getForResultSet() {
		return forResultSet;
	}

	public ForUpdate getForUpdate() {
		return forUpdate;
	}

	public ForDDL getForDDL() {
		return forDDL;
	}

	public ForJSON getForJSON() {
		return forJSON;
	}

	public ForProcedure getForProcedure() {
		return forProcedure;
	}

	public void queryForResultSet(String sql, JSONObject param, QueryCallBackHandler queryCallBackHandler) throws Exception {
		forResultSet.sql(sql, param, queryCallBackHandler);
	}

	public void queryIdForResultSet(String xmlId, JSONObject param, QueryCallBackHandler queryCallBackHandler) throws Exception {
		forResultSet.queryId(xmlId, param, queryCallBackHandler);
	}

	public  boolean tableExists(String tableName) throws Exception {
    	return this.cn.tableExists(tableName);
	}

	public  boolean functionExists(String tableName) throws Exception {
		return this.cn.functionExists(tableName);
	}

	public  boolean procedureExists(String tableName) throws Exception {
		return this.cn.procedureExists(tableName);
	}

	public  boolean sequenceExists(String sequence) throws Exception {
		return !forResultSet.queryIsEmpty("SELECT 1 AS TS FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '"+sequence+"'");
	}

	public  ArrayList<String> getSqlColumn(String sql) throws Exception {
		sql=sql.replace("?","null");
		return this.cn.getSqlColumn(sql);
	}

	public Table getSqlTable(String sql) throws Exception {
		String xmlsql="<select>"+iniSql(sql)+"</select>";
		XmlPaste xml=new XmlPaste(xmlsql);
		xml.setSession(this);
		xml.expainSql(sql, SqlNodeVar.SqlType.XML_SQL, null);
		return this.cn.getSqlTable(finiSql(xml.getExecSql(new ConditionMap(new JSONObject()))));
	}

	public JSONObject getTableInfo(String tableName) throws Exception {
		return this.cn.getTableInfo(tableName);
	}

	public  String getTableRemark(String tableName) throws Exception {
		JSONObject m=this.cn.getTableInfo(tableName);
		if(m==null) return null;
		return m.getString("REMARKS");
	}

	public  String getColumnRemark(String colname, String tableName) throws Exception {
		return this.cn.getColumnRemark(colname, tableName);
	}

	public  JSONObject getAllTable() throws Exception {
		return this.cn.getAllTable();
	}

	public void qryTable(TableInfoCallback tableInfoCallback) throws Exception {
		this.cn.qryTable(tableInfoCallback);
	}

	public SpecialField.Field getSpecialField() throws Exception {
		if(sysField==null){
			if(theme==null) throw new Exception("请先设置theme！");
			sysField= LocalVariable.getConfig().getSpecialField(theme);
		}
		return sysField;
	}

	public String getTheme() {
		return theme;
	}

	public void setTheme(String theme) {
		if(DBUtil.getSysSingleSysId()!=null) this.theme=DBUtil.getSysSingleSysId().toLowerCase();
		else this.theme = theme.toLowerCase();
	}

	public MetaData getMetaData() throws Exception {
		Connection conn=getConnection();
		if(conn==null || conn.isClosed()){
			setConnection(null);
			conn=getConnection();
		}
		return new MetaData(conn, getSchema());
	}

	public Tables getTables(String tabname) throws Exception {
		MetaData db=getMetaData();
		Tables tables=db.getTables(tabname);
		tables.expain(this);
		return tables;
	}

	public Var getVar(SqlResultSet rs) throws Exception {
		int len=rs.getMetaData().getColumnCount();
		String col="";
		String var="";
		for(int i=1; i<=len; i++){
			String c=rs.getMetaData().getColumnName(i);
			if(i>1){
				col=col+",";
				var=var+",";
			}
			col=col+c;
			var=var+"?";
		}
		Var v=new Var();
		v.setCol(col);
		v.setVar(var);
		return v;
	}

	/**
	 * 将XLS内容导入至数据库，支持自定义执行逻辑配置，可与执行过程做关联
	 * @param id  XML配置中xlsRead结点对应的id
	 * @param xlsFile  要导入的文件名
	 * @throws Exception
	 */
	public void importXls(String id, String xlsFile) throws Exception {
		importXls(id, xlsFile, null, null);
	}

	public void importXls(String id, String xlsFile, JSONObject input) throws Exception {
		importXls(id, xlsFile, input, null);
	}

	public void importXls(String id, String xlsFile, XLSReadCallback xlsReadCallback) throws Exception {
		importXls(id, xlsFile, null, xlsReadCallback);
	}

	public void importXls(String id, String xlsFile, JSONObject input, XLSReadCallback xlsReadCallback) throws Exception {
		XLSRead xls=new XLSRead();
		xls.setXlsTable(ConditionUtil.getXls(id));
		xls.setXlsReadCallback(xlsReadCallback);
		xls.setSession(this);
		if(input==null){
			xls.readExcel(xlsFile);
		} else {
			xls.readExcel(xlsFile, input);
		}
	}

	public void importXls(XLSTable xlsTable, String xlsFile) throws Exception {
		importXls(xlsTable, xlsFile, null, null);
	}

	public void importXls(XLSTable xlsTable, String xlsFile, XLSReadCallback xlsReadCallback) throws Exception {
		importXls(xlsTable, xlsFile, null, xlsReadCallback);
	}

	public void importXls(XLSTable xlsTable, String xlsFile, JSONObject input, XLSReadCallback xlsReadCallback) throws Exception {
		XLSRead xls=new XLSRead();
		xls.setXlsTable(xlsTable);
		xls.setXlsReadCallback(xlsReadCallback);
		xls.setSession(this);
		if(input==null){
			xls.readExcel(xlsFile);
		} else {
			xls.readExcel(xlsFile, input);
		}
	}

	public void importXlsFromPath(String id, String path, JSONObject input) throws Exception {
		XLSRead xls=new XLSRead();
		xls.setXlsTable(ConditionUtil.getXls(id));
		xls.setSession(this);
		ArrayList<String> flist=FileUtil.getFieldList(path);
		int len=flist.size();
		for(int i=0; i<len; i++){
			xls.readExcel(flist.get(i), input);
		}
	}

	public void importXlsFromZip(String id, String zipFile) throws Exception {
		String path=FileUtil.unZip(zipFile);
		ArrayList<String> flist=FileUtil.getFieldList(path);
		if(flist!=null){
			int len=flist.size();
			for(int i=0; i<len; i++){
				importXls(id, flist.get(i));
			}
		}
	}

	public void importXlsFromRar(String id, String zipFile) throws Exception {
		String path=FileUtil.unRar(zipFile);
		ArrayList<String> flist=FileUtil.getFieldList(path);
		if(flist!=null){
			int len=flist.size();
			for(int i=0; i<len; i++){
				importXls(id, flist.get(i));
			}
		}
	}

	/**
	 * 根据表名生成XLS，支持多张表/SQL导出，同时每个表/SQL占用一个SHEET，导出内容为表头，不包含内容
	 * @param tables 要导出对应的表名，如果存在多个，中间用,做为分隔符
	 * @param xlsFile 要生成的XLS文件名
	 * @throws Exception
	 */
	public void exportHeadXls(String tables, String xlsFile) throws Exception {
		XLSRead xls=new XLSRead();
		xls.setSession(this);
        xls.exportTableToXls(tables, xlsFile);
	}

	public int update_T(String sql, JSONObject param) throws Exception {
		return execSql_T(sql, param, new ExecSqlCallBackHandler(){
			@Override
			public int execSql(String sql, JSONObject param) throws Exception {
				return getForUpdate().update(sql, param);
			}
		});
	}

	public int saveTable_T(String tabName, JSONObject param) throws Exception {
		return execSql_T(tabName, param, new ExecSqlCallBackHandler(){
			@Override
			public int execSql(String sql, JSONObject param) throws Exception {
				return getForUpdate().saveTable(sql, param);
			}
		});
	}

	public int insertTable_T(String tabName, JSONObject param) throws Exception {
		return execSql_T(tabName, param, new ExecSqlCallBackHandler(){
			@Override
			public int execSql(String sql, JSONObject param) throws Exception {
				return getForUpdate().insertTable(sql, param);
			}
		});
	}

	public int updateTable_T(String tabName, JSONObject param) throws Exception {
		return execSql_T(tabName, param, new ExecSqlCallBackHandler(){
			@Override
			public int execSql(String sql, JSONObject param) throws Exception {
				return getForUpdate().updateTable(sql, param);
			}
		});
	}

	public int deleteTable_T(String tabName, JSONObject param) throws Exception {
		return execSql_T(tabName, param, new ExecSqlCallBackHandler(){
			@Override
			public int execSql(String sql, JSONObject param) throws Exception {
				return getForUpdate().deleteTable(sql, param);
			}
		});
	}

	private int execSql_T(String sql, JSONObject param, ExecSqlCallBackHandler execSqlCallBackHandler) throws Exception {
		Connection conn=null;
		Connection o_conn=null;
		try{
			o_conn=getConnection();
			conn=dbInfo.getDataSource().getConnection();
			setConnection(conn);
			conn.setAutoCommit(false);
			int i=execSqlCallBackHandler.execSql(sql, param);
			conn.commit();
			return i;
		} catch (Exception e){
			if(conn!=null) {
				conn.rollback();
			}
			throw e;
		} finally {
			if(conn!=null) {
				conn.close();
			}
			setConnection(o_conn);
		}
	}

	public int saveTabCache(String key, byte[] value) throws Exception{
		this.cn.delTabCache(key);
		int t=this.cn.saveTabCache(key, value);
		return t;
	}

	public int delTabCache(String key) throws Exception{
		int t=this.cn.delTabCache(key);
		return t;
	}

	public byte[] queryTabCache(String key) throws Exception {
		return this.cn.queryTabCache(key);
	}

	public boolean hasTabCache(String key) throws Exception {
		return this.cn.hasTabCache(key);
	}

}
